# encoding:utf-8
# user: ares at 18-9-21

import pymysql

MYSQL_HOST="127.0.0.1"
MYSQL_PORT=3306
MYSQL_USER='root'
MYSQL_PWD='root'
MYSQL_DB='house_db'

qyFilter=" quyu in ('福田','罗湖','南山','盐田','宝安','龙华','龙岗','大鹏','光明','坪山') "

conn=pymysql.connect(host=MYSQL_HOST,port=MYSQL_PORT,user=MYSQL_USER,passwd=MYSQL_PWD,db=MYSQL_DB)
cur=conn.cursor()


class ReportSQL():

    # 获取各个区的平均房价
    @classmethod
    def getAvgPrice(cls,laiyuan=''):
        sql="select quyu,sum(zongje)/sum(mianji) as avgprice from ershoufang where " + qyFilter

        #如果需要区分数据来源
        if laiyuan!='' :
            sfilter=" and laiyuan=%(laiyuan)s "

            value = {
                'laiyuan': laiyuan
            }
        else:
            sfilter=''
            value=''

        sGroup=" group by quyu order by avgprice desc "
        strSql=sql+sfilter+ sGroup
        try:
            cur.execute(strSql,value)
            result=cur.fetchall()
        except:
            print('错误：查询错误！！！')

        # finally:
        #     cur.close()
        #     conn.close()

        return result

    #获取房源数
    @classmethod
    def getFangYuanCount(self,laiyuan=''):
        sql="select quyu,count(*) as fcount from ershoufang where " + qyFilter

        if laiyuan!='':
            sfilter=" and laiyuan =%(laiyuan)s "

            value={
                'laiyuan':laiyuan
            }

        else:
            sfilter=''
            value=''

        sGroup=" group by quyu "
        strSql=sql+sfilter+sGroup
        try:
            cur.execute(strSql,value)
            result=cur.fetchall()
        except:
            print('错误：获取房源数错误！！！')
        # finally:
        #     cur.close()
        #     conn.close()

        return result

    #获取房源总数
    @classmethod
    def getSumFangyuan(cls,laiyuan=''):
        sql="select count(*) as sumcount from ershoufang where " +qyFilter

        if laiyuan!='':
            sfliter=" and laiyuan=%(laiyuan)s "

            value={
                'laiyuan':laiyuan
            }
        else:
            sfliter=''
            value=''

        strSql=sql+sfliter
        #print(strSql)


        cur.execute(strSql, value)

        result = cur.fetchall()
        # try:
        #     cur.execute(strSql,value)
        #     result=cur.fetchall()
        # except:
        #     print('错误：获取房源总数出错！！！')
        # finally:
        #     cur.close()
        #     conn.close()

        return result